package JDBC;
import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import SwingModel.CmsQuiz;
import SwingModel.CmsQuizQuestion;


public class InstructorQuizActivities {

	public static void postQuiz(String courseID, CmsQuiz quiz){
		System.out.println("The post new Quiz activitiy is called");
		System.out.println(quiz.getQuizTitle());
		
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			
			pstmt = dCon.connection.prepareStatement(
		"insert into tests (CourseId, TestTitle, TestText, MaxGrade, PercWeight, CreatedDTM, TestAvailDTM, TestDueDTM, TestExpDTM, TestDurationMin)"
		+ "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
            

			pstmt.setString(1,courseID);
			
			pstmt.setString(2,quiz.getQuizTitle());
			
			pstmt.setString(3,quiz.getQuizDescription());
			
			pstmt.setDouble(4, quiz.getMaxPoint());	
			
			pstmt.setDouble(5, quiz.getPercentWeight());
			
			SimpleDateFormat sdf = new SimpleDateFormat("YYYY:MM:dd HH:mm:ss");
			pstmt.setString(6, sdf.format(quiz.getCreatedDate()));
			
			pstmt.setString(7, sdf.format(quiz.getAvaiDate()));
			
			pstmt.setString(8, sdf.format(quiz.getDueDate())); 
			
			pstmt.setString(9, sdf.format(quiz.getExpDate()));
			
			pstmt.setInt(10, quiz.getDuration());
			
			pstmt.execute();
			
			System.out.println(quiz.getQuizTitle() + "Quiz is added to the DB");
			
			pstmt = dCon.connection.prepareStatement(
					//"select * from tests where CourseId=? and TestTitle=?");
					"select * from tests where CourseId=? and CreatedDTM=?");
			pstmt.setString(1,courseID);
			//pstmt.setString(2,  quiz.getQuizTitle());
			pstmt.setString(2, sdf.format(quiz.getCreatedDate()));
			ResultSet resultSet = pstmt.executeQuery();
			int quizID=0;
			
			for(int i = 0; resultSet.next(); i++)
			{
				quizID = resultSet.getInt("TestID");
				System.out.println(+quizID);
			}
			
			List<CmsQuizQuestion> questions = quiz.getQuestions();
			
			for(CmsQuizQuestion q : questions){
				
				pstmt=dCon.connection.prepareStatement(
						"insert into test_questions (TestId, QuestText, QuestKey, QuestAText, QuestBText,QuestCText,QuestDText,QuestEText)" 
				        + "values (?, ?, ?, ?, ?, ?, ?, ?);");
				
				pstmt.setInt(1,quizID);
				pstmt.setString(2,q.getQuestion());
				pstmt.setString(3,String.valueOf(q.getKey()));
				
				List<String> options = q.getOptions();
				
				if(options.size()>=1){ 
					pstmt.setString(4,options.get(0));
				}
				else pstmt.setNull(4,Types.CHAR);
				
				if(options.size()>=2){ 
					pstmt.setString(5,options.get(1));
				}
				else pstmt.setNull(5,Types.CHAR);
				
				if(options.size()>=3){ 
					pstmt.setString(6,options.get(2));
				}
				else pstmt.setNull(6,Types.CHAR);
				
				if(options.size()>=4){ 
					pstmt.setString(7,options.get(3));
				}
				else pstmt.setNull(7,Types.CHAR);
				
				if(options.size()>=5){ 
					pstmt.setString(8,options.get(4));
				}
				else pstmt.setNull(8,Types.CHAR);
				
				pstmt.execute();
				System.out.println(quiz.getQuizTitle() + "question is added to the DB");
				
			}

			System.out.println("done execution");
		} catch(Exception e){
			e.printStackTrace();
	}

	}


	public static CmsQuiz retrieveQuiz(String courseId, int quizID){
		// Add the code to retrieve the Quiz from DB
		CmsQuiz result = null;
        
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			
			pstmt = dCon.connection.prepareStatement(
					"select * from tests where CourseId=? and TestID=?");
			pstmt.setString(1,courseId);
			pstmt.setInt(2,  quizID);
			ResultSet resultSet = pstmt.executeQuery();
			
			
			
			for(int i = 0; resultSet.next(); i++)
			{
				System.out.println("InstructorQuizActivities.retriveQuiz -Inside ResultSet");
				List<CmsQuizQuestion> questions = new ArrayList<CmsQuizQuestion>();
				
				pstmt = dCon.connection.prepareStatement(
						"select * from test_questions where TestID=?");
				pstmt.setInt(1,quizID);
				ResultSet questionsResultSet = pstmt.executeQuery();
				for(int j = 0; questionsResultSet.next(); j++)
				{
					List<String> options = new ArrayList<String>();
					options.add(questionsResultSet.getString("QuestAText"));
					options.add(questionsResultSet.getString("QuestBText"));
					options.add(questionsResultSet.getString("QuestCText"));
					options.add(questionsResultSet.getString("QuestDText"));
					options.add(questionsResultSet.getString("QuestEText"));
					CmsQuizQuestion cmsQuizQuestion = new CmsQuizQuestion(questionsResultSet.getInt("TestQuestId"), questionsResultSet.getString("QuestText"), 
							questionsResultSet.getString("QuestKey").charAt(0), options); 
					questions.add(cmsQuizQuestion);
				}
				
				
				result = new CmsQuiz(quizID, resultSet.getString("TestTitle"), resultSet.getString("TestText"), resultSet.getDouble("MaxGrade"), 
						resultSet.getDouble("PercWeight"),resultSet.getTimestamp("CreatedDTM"), resultSet.getTimestamp("TestAvailDTM"), resultSet.getTimestamp("TestDueDTM"), 
						resultSet.getTimestamp("TestExpDTM"), resultSet.getInt("TestDurationMin"), questions, false);
				
				System.out.println(result.getQuizTitle());
			}
			
			System.out.println("done execution");
			} 
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return result;
		
	}
	
	public static void editQuiz(int quizID, String courseID, CmsQuiz quiz){
		System.out.println("Edit quizID: " + quizID);
		
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement(
					"delete from test_questions where TestID = ?");
			pstmt.setInt(1, quizID);
			
			pstmt.execute();
            
			pstmt = dCon.connection.prepareStatement(
					"update tests SET CourseId=?, TestTitle=?, TestText=?, MaxGrade=?, PercWeight=?, CreatedDTM=?, TestAvailDTM=?, TestDueDTM=?, TestExpDTM=?, TestDurationMin=? where TestID = ?");
			
			pstmt.setString(1,courseID);
			
			pstmt.setString(2,quiz.getQuizTitle());
			
			pstmt.setString(3,quiz.getQuizDescription());
			
			pstmt.setDouble(4, quiz.getMaxPoint());	
			
			pstmt.setDouble(5, quiz.getPercentWeight());
			
			SimpleDateFormat sdf = new SimpleDateFormat("YYYY:MM:dd HH:mm:ss");
			pstmt.setString(6, sdf.format(quiz.getCreatedDate()));
			
			pstmt.setString(7, sdf.format(quiz.getAvaiDate()));
			
			pstmt.setString(8, sdf.format(quiz.getDueDate())); 
			
			pstmt.setString(9, sdf.format(quiz.getExpDate()));
			
			pstmt.setInt(10, quiz.getDuration());
			
			pstmt.setInt(11, quizID);
			
			pstmt.execute();
			
			System.out.println("updated quizID: " + quizID);
			
			List<CmsQuizQuestion> questions = quiz.getQuestions();
			
			for(CmsQuizQuestion q : questions){
				
				pstmt=dCon.connection.prepareStatement(
						"insert into test_questions (TestId, QuestText, QuestKey, QuestAText, QuestBText,QuestCText,QuestDText,QuestEText)" 
				        + "values (?, ?, ?, ?, ?, ?, ?, ?);");
				
				pstmt.setInt(1,quizID);
				pstmt.setString(2,q.getQuestion());
				pstmt.setString(3,String.valueOf(q.getKey()));
				
				List<String> options = q.getOptions();
				
				if(options.size()>=1){ 
					pstmt.setString(4,options.get(0));
				}
				else pstmt.setNull(4,Types.CHAR);
				
				if(options.size()>=2){ 
					pstmt.setString(5,options.get(1));
				}
				else pstmt.setNull(5,Types.CHAR);
				
				if(options.size()>=3){ 
					pstmt.setString(6,options.get(2));
				}
				else pstmt.setNull(6,Types.CHAR);
				
				if(options.size()>=4){ 
					pstmt.setString(7,options.get(3));
				}
				else pstmt.setNull(7,Types.CHAR);
				
				if(options.size()>=5){ 
					pstmt.setString(8,options.get(4));
				}
				else pstmt.setNull(8,Types.CHAR);
				
				pstmt.execute();
				System.out.println(quiz.getQuizID() + "is edited");

		
			}
			}
		catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public static List<CmsQuiz> getListOfQuiz(String courseID){
		List<CmsQuiz> listOfQuiz = new ArrayList<CmsQuiz>();
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("select * from tests where courseID=? order by CreatedDTM asc");
			pstmt.setString(1,courseID);
			ResultSet resultSet = pstmt.executeQuery();
			List<CmsQuizQuestion> questions = new ArrayList<CmsQuizQuestion>();

			for(int i = 0; resultSet.next(); i++){
				System.out.println("InstructorQuizActivities.getListOfQuiz -Inside ResultSet");
				int testId= resultSet.getInt("TestID");
				//add quiz to the list 
				CmsQuiz quiz =new CmsQuiz(testId, resultSet.getString("TestTitle"), resultSet.getString("TestText"), resultSet.getDouble("MaxGrade"), 
						resultSet.getDouble("PercWeight"),resultSet.getTimestamp("CreatedDTM"), resultSet.getTimestamp("TestAvailDTM"), resultSet.getTimestamp("TestDueDTM"), 
						resultSet.getTimestamp("TestExpDTM"), resultSet.getInt("TestDurationMin"), questions, false);
				listOfQuiz.add(quiz);
				
			}
		} catch(Exception e){}
		
		return listOfQuiz;
		
	}	
	public static void deleteQuiz(int quizID){
		System.out.println("Delete quizID: " + quizID);
		
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement(
					"delete from test_questions where TestID = ?");
			pstmt.setInt(1, quizID);
			
			pstmt.execute();
			
			pstmt = dCon.connection.prepareStatement(
					"delete from user_tests where TestID = ?");
			pstmt.setInt(1, quizID);
			
			pstmt.execute();
            
			pstmt = dCon.connection.prepareStatement(
					"delete from tests where TestID = ?");
			pstmt.setInt(1, quizID);
			
			pstmt.execute();
		} catch(Exception e){
			e.printStackTrace();
		}
	}
}


	
